package sharping.appilog.LocalDB;

import java.util.ArrayList;

import sharping.appilog.app;
import sharping.appilog.DataTree.NotificationData;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class PhoneDB extends SQLiteOpenHelper {

	public PhoneDB(Context context) {
		super(context, "Notifications", null, 1);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		String Notification_Table = "create table notification " + "("
				+ "idx INTEGER PRIMARY KEY AUTOINCREMENT," + "type INTEGER," 
				+ "user_id TEXT,"
				+ "content_id TEXT,"
				+ "to_id TEXT," + "to_name TEXT," + "from_id TEXT,"
				+ "from_name TEXT," + "content TEXT," + "ischecked INTEGER,"
				+ "reg_date DATETIME DEFAULT (datetime('now','localtime'))"
				+ ");";

		String Search_Table = "create table search " + "("
				+ "idx INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "search_text TEXT,"
				+ "reg_date DATETIME DEFAULT (datetime('now','localtime'))"
				+ ");";
		db.execSQL(Notification_Table);
		db.execSQL(Search_Table);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
		// TODO Auto-generated method stub
		db.execSQL("drop table if exists notification");
		db.execSQL("drop table if exists search");
	}

	public void setNotificationData(NotificationData item) {
		String Query = "insert into notification " + "(" + "idx," + "type," + "user_id," + "content_id,"
				+ "to_id," + "to_name," + "from_id," + "from_name,"
				+ "content," + "ischecked," + "reg_date" + ") " + "values ("
				+ "null," + "'" + item.getType() + "'," + "'" + app.UserId + "'," + "'" + item.getContentId() + "'," + "'" + item.getTo_id()
				+ "'," + "'" + item.getTo_name() + "'," + "'"
				+ item.getFrom_id() + "'," + "'" + item.getFrom_name() + "',"
				+ "'" + item.getContent() + "'," + "'" + 0 + "'," + "null);";
		Log.v("Query", Query);
		getWritableDatabase().execSQL(Query);

	}

	public void updateNotification(int idx) {
		String Query = "update notification set" + "ischecked = 1" + "where "
				+ "idx = " + idx + ";";

		getWritableDatabase().execSQL(Query);
	}

	public ArrayList<NotificationData> getNotificationData() {
		ArrayList<NotificationData> list = new ArrayList<NotificationData>();
		String Query = "select * from notification " + "where ischecked = 0 "
				+ "order by reg_date desc";
		Cursor cursor = getReadableDatabase().rawQuery(Query, null);

		while (cursor.moveToNext()) {
			NotificationData item = new NotificationData();
			item.setIdx(cursor.getInt(0));
			item.setType(cursor.getInt(1));
			item.setUserId(cursor.getString(2));
			item.setContentId(cursor.getString(3));
			item.setTo_id(cursor.getString(4));
			item.setTo_name(cursor.getString(5));
			item.setFrom_id(cursor.getString(6));
			item.setFrom_name(cursor.getString(7));
			item.setContent(cursor.getString(8));
			item.setIschecked(cursor.getInt(9));
			item.setReg_date(cursor.getString(10));

			list.add(item);
		}
		return list;
	}
	
	public void removeNotificationData() {
		String Query = "delete from notification where reg_date < date_add(now(), 'INTERVAL -7 day');";
		// getWritableDatabase().execSQL(Query);
	}
}
